﻿CREATE PROCEDURE [util].[JobOutputCleanupByName]
@FileRetentionInDays TINYINT=7, @StartDate DATETIME=NULL, @EndDate DATETIME=NULL, @FilePrefix NVARCHAR (50)=N'Job_Output_'
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF (ISNULL(@FileRetentionInDays,0) < 1)
	AND(DATEDIFF(day,ISNULL(@StartDate,GETDATE()),GETDATE()) = 1)
		BEGIN
			RAISERROR('Minimum file retention is 1 day',16,1)
			SET @ERROR = @@ERROR
			GOTO ERROR_HANDLER
		END

	DECLARE @SQLDir nvarchar(256)
	EXEC @RC = [util].[GetDefaultLogPath] @SQLDir OUTPUT
	IF(@RC <> 0) GOTO ERROR_HANDLER

	IF (@StartDate IS NULL)
		SET @StartDate = DATEADD(day,@FileRetentionInDays * -1,GETDATE())
	IF (@EndDate IS NULL)
		SET @EndDate = @StartDate

	DECLARE @DeleteDate datetime
	SET @DeleteDate = @StartDate

	WHILE(@DeleteDate >= @EndDate)
		BEGIN
			DECLARE @DateString nchar(8)
			SET @DateString = CONVERT(nchar(8),@DeleteDate,112)

			DECLARE @CmdStatement nvarchar(512)
			SET @CmdStatement = N'DEL "' 
				+ @SQLDir 
				+ N'\LOG\'
				+ ISNULL(@FilePrefix,N'')
				+ N'*'
				+ @DateString
				+ N'.txt"'

			PRINT N'@CmdStatement = ' + ISNULL(@CmdStatement,N'')

			EXEC @RC = master.dbo.xp_cmdshell @CmdStatement, NO_OUTPUT
			IF(@RC <> 0) GOTO ERROR_HANDLER

			SET @DeleteDate = DATEADD(day,-1,@DeleteDate)
		END
	
	GOTO EXIT_PROC

ERROR_HANDLER:
	IF(@RC = 0)
		SET @RC = @ERROR

EXIT_PROC:
	RETURN(@RC)
END